package com.carlinfo.demo.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.carlinfo.common.util.DbUtil;
import com.carlinfo.common.util.PageInfoUtil;
import com.carlinfo.demo.dao.IADynastyDao;
import com.carlinfo.demo.pojo.ADynasty;

public class ADynastyDaoImpl implements IADynastyDao
{
	private Logger logger = LogManager.getLogger();

	private ADynasty converFromDb(ResultSet rs) throws SQLException
	{
		int id = rs.getInt("id");
		String name = rs.getString("name");
		String capital = rs.getString("capital");
		String context = rs.getString("context");
		int age = rs.getInt("age");
		byte status = rs.getByte("status");
		Date st = rs.getTimestamp("st");
		Date createTime = rs.getTimestamp("createTime");
		Date updateTime = rs.getTimestamp("updateTime");
		Date pubTime = rs.getTimestamp("pubTime");
		/*
		 * 不能打印,要返回 new出来的属性全部是空的; 将数据库取出来的值,一一赋值给属性
		 */
		ADynasty dynasty = new ADynasty();
		dynasty.setId(id);
		dynasty.setName(name);
		dynasty.setContext(context);
		dynasty.setCapital(capital);
		dynasty.setSt(st);
		dynasty.setAge(age);
		dynasty.setStatus(status);
		dynasty.setCreateTime(createTime);
		dynasty.setUpdateTime(updateTime);
		dynasty.setPubTime(pubTime);
		return dynasty;

	}

	@Override
	public int saveOneDao(ADynasty dynasty)
	{
		String sql = "insert into a_dynasty value(null,?,?,?,?,?,?,?,?,?)";
		DbUtil dbUtil = DbUtil.getInstance();
		Connection conn = dbUtil.getConn();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try
		{
			pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pst.setObject(1, dynasty.getName());
			pst.setObject(2, dynasty.getCapital());
			pst.setObject(3, dynasty.getAge());
			pst.setObject(4, dynasty.getSt());
			pst.setObject(5, dynasty.getContext());
			pst.setObject(6, dynasty.getStatus());
			pst.setObject(7, dynasty.getCreateTime());
			pst.setObject(8, dynasty.getUpdateTime());
			pst.setObject(9, dynasty.getPubTime());
			int res = pst.executeUpdate();
			rs = pst.getGeneratedKeys();
			if (rs.next())
			{
				int id = rs.getInt(1);
				dynasty.setId(id);
			}
			return res;
		} catch (SQLException e)
		{
			e.printStackTrace();
			logger.error("朝代操作:查询多条记录失败", e);
		} finally
		{
			dbUtil.closeAll(conn, pst, rs);
		}
		return 0;
	}

	@Override
	public int updateOneDao(ADynasty dynasty)
	{
		String sql = "update a_dynasty set name = ? , capital = ? , " + "age = ? , st = ? , context = ? , status = ? , "
				+ "createTime = ? , updateTime = ? , pubTime = ? " + "where id = ? ";
		DbUtil dbUtil = DbUtil.getInstance();
		Connection conn = dbUtil.getConn();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try
		{
			pst = conn.prepareStatement(sql);
			/* 为占位符赋值 */
			/* 为占位符赋值 */
			pst.setObject(1, dynasty.getName());
			pst.setObject(2, dynasty.getCapital());
			pst.setObject(3, dynasty.getAge());
			pst.setObject(4, dynasty.getSt());
			pst.setObject(5, dynasty.getContext());
			pst.setObject(6, dynasty.getStatus());
			pst.setObject(7, dynasty.getCreateTime());
			pst.setObject(8, dynasty.getUpdateTime());
			pst.setObject(9, dynasty.getPubTime());
			pst.setObject(10, dynasty.getId());
			/* 执行SQL语句 */
			int res = pst.executeUpdate();
			/* 一定要返回 */
			return res;
		} catch (SQLException e)
		{
			// e.printStackTrace();
			logger.info("更新失败", e);
		} finally
		{
			dbUtil.closeAll(conn, pst, rs);
		}
		return 0;
	}

	@Override
	public int deleteOneDao(Map<String, Object> condMap)
	{
		List<Object> paramsList = new ArrayList<Object>();
		String sql = "delete from a_dynasty";
		if (condMap.get("id") != null && !"".equalsIgnoreCase(condMap.get("id") + ""))
		{
			sql = sql + " where id = ?";
			paramsList.add(condMap.get("id"));
		} else if (condMap.get("name") != null && !"".equalsIgnoreCase(condMap.get("name") + ""))
		{
			sql = sql + " where name = ? ";
			paramsList.add(condMap.get("name"));
		}
		DbUtil dbUtil = DbUtil.getInstance();
		Connection conn = dbUtil.getConn();
		PreparedStatement pst = null;
		try
		{
			this.logger.info("sql:{},paramList:{}", sql, paramsList);
			pst = conn.prepareStatement(sql);
			int count = 1;
			for (Iterator iterator = paramsList.iterator(); iterator.hasNext();)
			{
				Object val = (Object) iterator.next();
				pst.setObject(count, val);
				count++;
			}
			int res = pst.executeUpdate();
			return res;
		} catch (SQLException e)
		{
			this.logger.error("朝代删除操作失败了;", e);
		} finally
		{
			dbUtil.closeAll(conn, pst, null);
		}
		return 0;
	}

	@Override
	public ADynasty selectOneDao(Map<String, Object> condMap)
	{
		List<Object> paramsList = new ArrayList<Object>();
		String sql = "select * from a_dynasty ";
		if (!"".equalsIgnoreCase(condMap.get("id") + "") && condMap.get("id") != null)
		{
			sql = sql + " where id = ?";
			paramsList.add(condMap.get("id"));
		} else if (!"".equalsIgnoreCase(condMap.get("name") + "") && condMap.get("name") != null)
		{
			sql = sql + " where name =?";
			paramsList.add(condMap.get("name"));
		}
		DbUtil dbUtil = DbUtil.getInstance();
		Connection conn = dbUtil.getConn();
		PreparedStatement pst = null;
		ResultSet rs = null;
		this.logger.info("sql:{},paramList:{}", sql, paramsList);
		try
		{
			pst = conn.prepareStatement(sql);
			int count = 1;
			for (Iterator iterator = paramsList.iterator(); iterator.hasNext();)
			{
				Object val = (Object) iterator.next();
				pst.setObject(count, val);
				count++;
			}
			rs = pst.executeQuery();
			if (rs.next())
			{
				ADynasty dynasty = converFromDb(rs);
				return dynasty;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			dbUtil.closeAll(conn, pst, rs);
		}
		return null;
	}
	/**
	 	 * 分页:
	 	 * ~想办法把SQL语句上,加上limit
	 * 
	 * ~如何根据condMap(存储的是查询条件)组成以下的sql语句呢?
关键字:		select * from a_dynasty where (name like '%0%' or capital like '%0%' or context like '%0%');
状态	:		select * from a_dynasty where status = 1 ; 
时间	:		select * from a_dynasty where (pubTime >= '2018-09-18 00:00:00' and pubTime < '2018-09-19 10:00:00');
关键字+状态:	select * from a_dynasty where (name like '%0%' or capital like '%0%' or context like '%0%') and status = 1 ;
关键字+时间:	select * from a_dynasty where (name like '%0%' or capital like '%0%' or context like '%0%') and (pubTime >= '2018-09-18 00:00:00' and pubTime < '2018-09-19 10:00:00');
时间+状态:		select * from a_dynasty where status = 1 and (pubTime >= '2018-09-18 00:00:00' and pubTime < '2018-09-19 10:00:00');
关键字+状态+时间:select * from a_dynasty where (name like '%0%' or capital like '%0%' or context like '%0%') and status = 1 and (pubTime >= '2018-09-18 00:00:00' and pubTime < '2018-09-19 10:00:00');
啥也不选:		select * from a_dysnasty ; 
	 	 */
	@Override
	public List<ADynasty> selectListDao(PageInfoUtil pageInfoUtil, Map<String, Object> condMap)
	{
		List<Object> paramsList = new ArrayList<Object>();
		List<ADynasty> dynastyList = new ArrayList<ADynasty>();
		String sql = "select * from a_dynasty where 1=1 ";
		if (condMap.get("keyword")!=null && !"".equalsIgnoreCase(condMap.get("keyword")+""))
		{
			sql = sql +" and(name like ? or capital like ? or context like ?)";
			paramsList.add("%"+ condMap.get("keyword") +"%");
			paramsList.add("%"+ condMap.get("keyword") +"%");
			paramsList.add("%"+ condMap.get("keyword") +"%");
		}
		if (condMap.get("status")!=null && !"".equalsIgnoreCase(condMap.get("status")+""))
		{
			sql+=" and status = ?";
			paramsList.add(condMap.get("status"));
		}
		if (condMap.get("stDate") instanceof Date && condMap.get("edDate") instanceof Date)
		{
			sql+= " and (pubTime >= ? and pubTime <? )";
			paramsList.add(condMap.get("stDate"));
			paramsList.add(condMap.get("edDate"));
		}
		
		/*排序*/
		if ("idAsc".equalsIgnoreCase(condMap.get("orderBy")+""))
		{
			/*id:升序*/
			sql += " order by id asc";
		}else if ("idAscNameDesc".equalsIgnoreCase(condMap.get("orderBy")+""))
		{
			
			sql += "order by id asc,name desc";
		}else
		{
			/*默认的效果*/
			sql += "order by pubTime desc";
		}
		DbUtil dbUtil = DbUtil.getInstance();
		Connection conn = dbUtil.getConn();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try
		{
			int count = 1;
			if (pageInfoUtil != null)
			{
				String fromIndex = "from";
				String countSQL = "select count(*) " + sql.substring(sql.indexOf(fromIndex), sql.length());
				
				this.logger.info("countSQL:{},参数：{}", countSQL,paramsList);
				/* 又要执行SQL语句 */
				pst = conn.prepareStatement(countSQL);
				/* 为占位符赋值 */
				count = 1;
				for (Iterator iterator = paramsList.iterator(); iterator.hasNext();)
				{
					Object val = (Object) iterator.next();
					pst.setObject(count, val);
					count++;
				}
				/* 执行sql语句 */
				rs = pst.executeQuery();
				if (rs.next())
				{
					int totalRecord = rs.getInt(1);
					/* 赶紧把查询出的总条数赋值给pageInfoUtil */
					pageInfoUtil.setTotalRecord(totalRecord);
				}
				rs.close();
				pst.close();

				/* 以下是正常测试的分页 */
				sql = sql + " limit ?,? ";
				paramsList.add(pageInfoUtil.getCurrentRecord());
				paramsList.add(pageInfoUtil.getPageSize());
			}
			this.logger.info("sql:{},参数:{}", sql, paramsList);
			pst = conn.prepareStatement(sql);
			/* int count = 1; */
			count = 1;
			for (Iterator iterator = paramsList.iterator(); iterator.hasNext();)
			{
				Object val = (Object) iterator.next();
				pst.setObject(count, val);
				count++;
			}
			rs = pst.executeQuery();
			while (rs.next())
			{
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String capital = rs.getString("capital");
				String context = rs.getString("context");
				int age = rs.getInt("age");
				byte status = rs.getByte("status");
				Date st = rs.getTimestamp("st");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");
				Date pubTime = rs.getTimestamp("pubTime");
				ADynasty dynasty = new ADynasty();
				dynasty.setId(id);
				dynasty.setName(name);
				dynasty.setContext(context);
				dynasty.setCapital(capital);
				dynasty.setSt(st);
				dynasty.setAge(age);
				dynasty.setStatus(status);
				dynasty.setCreateTime(createTime);
				dynasty.setUpdateTime(updateTime);
				dynasty.setPubTime(pubTime);
				dynastyList.add(dynasty);
			}
			return dynastyList;
		} catch (SQLException e)
		{
			logger.error("朝代操作:查询多条记录失败", e);
		} finally
		{
			dbUtil.closeAll(conn, pst, rs);
		}
		return Collections.emptyList();
	}

}
